const DB = require('../tools/mysql')
var moment = require('moment')

module.exports = async ctx => {

  const { userid, type, table, cityid } = ctx.request.body

  if (table == 0) {

    const where = type ? { 'village_registry.inspector': userid } : {}
    const list = await DB('village_registry')
      .select(
        'village_registry.id',
        'village_registry.town',
        'village_registry.inspector',
        'village_registry.village',
        'village_registry.time',
        'p1.name as townname',
        'p1.pid as cityid',
        'p3.name as cityname',
        'p2.name as villagename',
        'user.name as inspectorname',
        'village_registry.state',
        'village_registry.edittime'
      )
      .leftJoin('placename as p1', 'village_registry.town', 'p1.id')
      .leftJoin('placename as p2', 'village_registry.village', 'p2.id')
      .leftJoin('placename as p3', 'p1.pid', 'p3.id')
      .leftJoin('user', 'village_registry.inspector', 'user.id')
      .where(where)
      .andWhere('enable', 1)
      .orderBy('village_registry.state')
      .orderBy('village_registry.time', 'desc')

    const data = list.filter(item => {
      item.time = moment(item.time).format('YYYY-MM-DD')
      item.edittime && (item.edittime = moment(item.edittime).format('YYYY-MM-DD HH:mm'))
      return cityid.split(',').indexOf(item.cityid + '') > -1
    })
    ctx.body = { code: 1, msg: '查询成功', data }

  } else if (table == 1) {

    const where = type ? { 'home_registy.inspector': userid } : {}
    const list = await DB('home_registy')
      .select(
        'home_registy.id',
        'home_registy.town',
        'home_registy.inspector',
        'home_registy.village',
        'home_registy.time',
        'p1.name as townname',
        'p1.pid as cityid',
        'p3.name as cityname',
        'p2.name as villagename',
        'user.name as inspectorname',
        'home_registy.state',
        'home_registy.edittime'
      )
      .leftJoin('placename as p1', 'home_registy.town', 'p1.id')
      .leftJoin('placename as p2', 'home_registy.village', 'p2.id')
      .leftJoin('placename as p3', 'p1.pid', 'p3.id')
      .leftJoin('user', 'home_registy.inspector', 'user.id')
      .where(where)
      .andWhere('enable', 1)
      .orderBy('home_registy.state')
      .orderBy('home_registy.time', 'desc')

    const data = list.filter(item => {
      item.time = moment(item.time).format('YYYY-MM-DD')
      item.edittime && (item.edittime = moment(item.edittime).format('YYYY-MM-DD HH:mm'))
      return cityid.split(',').indexOf(item.cityid + '') > -1
    })
    ctx.body = { code: 1, msg: '查询成功', data }

  } else if (table == 2) {

    const where = type ? { 'three_implementation.inspector': userid } : {}
    const list = await DB('three_implementation')
      .select(
        'three_implementation.id',
        'three_implementation.town',
        'three_implementation.inspector',
        'three_implementation.time',
        'p1.name as townname',
        'p1.pid as cityid',
        'p2.name as cityname',
        'user.name as inspectorname',
        'three_implementation.state',
        'three_implementation.edittime'
      )
      .leftJoin('placename as p1', 'three_implementation.town', 'p1.id')
      .leftJoin('placename as p2', 'p1.pid', 'p2.id')
      .leftJoin('user', 'three_implementation.inspector', 'user.id')
      .where(where)
      .andWhere('enable', 1)
      .orderBy('three_implementation.state')
      .orderBy('three_implementation.time', 'desc')

    const data = list.filter(item => {
      item.time = moment(item.time).format('YYYY-MM-DD')
      item.edittime && (item.edittime = moment(item.edittime).format('YYYY-MM-DD HH:mm'))
      return cityid.split(',').indexOf(item.cityid + '') > -1
    })
    ctx.body = { code: 1, msg: '查询成功', data }

  } else if (table == 3) {
    
  	const where = type ? { 'issue_list.inspector': userid } : {}
    const list = await DB('issue_list')
      .select(
        'issue_list.id',
        'issue_list.prefecture as cityid',
        'issue_list.town',
        'issue_list.inspector',
        'issue_list.time',
        'p1.name as townname',
        'p2.name as cityname',
        'user.name as inspectorname',
        'issue_list.state',
        'issue_list.edittime'
      )
      .leftJoin('placename as p1', 'issue_list.town', 'p1.id')
      .leftJoin('placename as p2', 'issue_list.prefecture', 'p2.id')
      .leftJoin('user', 'issue_list.inspector', 'user.id')
      .where(where)
      .andWhere('enable', 1)
      .orderBy('issue_list.state')
      .orderBy('issue_list.time', 'desc')

    const data = list.filter(item => {
      item.time = moment(item.time).format('YYYY-MM-DD')
      item.edittime && (item.edittime = moment(item.edittime).format('YYYY-MM-DD HH:mm'))
      return cityid.split(',').indexOf(item.cityid + '') > -1
    })
    ctx.body = { code: 1, msg: '查询成功', data }

  }


}